﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Common.Framework.Extensions;
using System.Data.Common;
using System.Diagnostics;

namespace DBUtil
{
    /// <summary>
    /// 通用数据库访问对象
    /// </summary>
    public abstract partial class DBAccess
    {
        #region 执行sql语句 ExecuteSql...

        public virtual int ExecuteSql(string sql)
            => ExecuteSql(sql, null, null, []);

        public virtual int ExecuteSql(string sql, IDictionary<string, object> parameters)
            => ExecuteSql(sql, null, null, parameters?.Keys.Select(key => CreatePara(key, parameters[key])).ToArray());

        public virtual int ExecuteSql(string sql, params DbParameter[] parameters)
            => ExecuteSql(sql, null, null, parameters);

        public virtual int ExecuteSql(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            Stopwatch st = null;
            Exception ex = null;
            if (Setting.HasMonitor) { st = new Stopwatch(); st.Start(); }
            try
            {
                return RunInCommand(cmd => cmd.ExecuteNonQuery(), sql, commandType, timeoutSecond, parameters);
            }
            catch (Exception e)
            {
                if (Setting.HasMonitor) ex = e;
                throw;
            }
            finally
            {
                if (Setting.HasMonitor)
                {
                    if (st.IsRunning) st.Stop();
                    Setting.MonitorAction?.Invoke(new MonitorArgument
                    {
                        Sql = sql,
                        Exception = ex,
                        TimeSpan = TimeSpan.FromMilliseconds(st.ElapsedMilliseconds)
                    }).Wait();
                }
            }
        }
        #endregion

        #region 查询语句 Select...

        #region SelectScalar
        public virtual T SelectScalar<T>(string sql) => SelectScalar<T>(sql, null, null, []);

        public virtual T SelectScalar<T>(string sql, IDictionary<string, object> parameters)
            => SelectScalar<T>(sql, null, null, parameters?.Select(i => CreatePara(i.Key, i.Value)).ToArray());

        public virtual T SelectScalar<T>(string sql, params DbParameter[] parameters)
            => SelectScalar<T>(sql, null, null, parameters);

        public virtual T SelectScalar<T>(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            Stopwatch st = null;
            Exception ex = null;
            if (Setting.HasMonitor) { st = new Stopwatch(); st.Start(); }
            try
            {
                return RunInCommand(cmd =>
                {
                    var res = cmd.ExecuteScalar();
                    return res.To<T>();
                }, sql, commandType, timeoutSecond, parameters);
            }
            catch (Exception e)
            {
                if (Setting.HasMonitor) ex = e;
                throw;
            }
            finally
            {
                if (Setting.HasMonitor)
                {
                    if (st.IsRunning) st.Stop();
                    Setting.MonitorAction?.Invoke(new MonitorArgument
                    {
                        Sql = sql,
                        Exception = ex,
                        TimeSpan = TimeSpan.FromMilliseconds(st.ElapsedMilliseconds)
                    }).Wait();
                }
            }
        }
        #endregion

        #region SelectDataSet
        public virtual DataSet SelectDataSet(string sql)
            => SelectDataSet(sql, null, null, []);

        public virtual DataSet SelectDataSet(string sql, params DbParameter[] parameters)
            => SelectDataSet(sql, null, null, parameters);

        public virtual DataSet SelectDataSet(string sql, IDictionary<string, object> parameters)
            => SelectDataSet(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual DataSet SelectDataSet(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            Stopwatch st = null;
            Exception ex = null;
            if (Setting.HasMonitor) { st = new Stopwatch(); st.Start(); }
            DataSet set = new();
            try
            {
                RunInCommand(cmd =>
                {
                    using var adp = CreateAdapter(cmd);
                    adp.Fill(set);
                }, sql, commandType, timeoutSecond, parameters);
                return set;
            }
            catch (Exception e)
            {
                if (Setting.HasMonitor) ex = e;
                throw;
            }
            finally
            {
                if (Setting.HasMonitor)
                {
                    if (st.IsRunning) st.Stop();
                    Setting.MonitorAction?.Invoke(new MonitorArgument
                    {
                        Sql = sql,
                        Exception = ex,
                        TimeSpan = TimeSpan.FromMilliseconds(st.ElapsedMilliseconds)
                    }).Wait();
                }
            }
        }
        #endregion

        #region SelectDataTable
        public virtual DataTable SelectDataTable(string sql)
            => SelectDataTable(sql, null, null, []);

        public virtual DataTable SelectDataTable(string sql, IDictionary<string, object> parameters)
            => SelectDataTable(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual DataTable SelectDataTable(string sql, params DbParameter[] parameters)
            => SelectDataTable(sql, null, null, parameters);

        public virtual DataTable SelectDataTable(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
            => SelectDataSet(sql, commandType, timeoutSecond, parameters).Tables[0];
        #endregion

        #region SelectDataReader

        #region SelectDataReader Action
        public virtual void SelectDataReader(Action<DBUtilDataReader> action, string sql)
            => SelectDataReader(action, sql, null, null, []);

        public virtual void SelectDataReader(Action<DBUtilDataReader> action, string sql, params DbParameter[] parameters)
            => SelectDataReader(action, sql, null, null, parameters);

        public virtual void SelectDataReader(Action<DBUtilDataReader> action, string sql, IDictionary<string, object> parameters)
            => SelectDataReader(action, sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual void SelectDataReader(Action<DBUtilDataReader> action, string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            if (action == null) throw new Exception("执行逻辑不能为空!");
            Stopwatch st = null;
            Exception ex = null;
            if (Setting.HasMonitor) { st = new Stopwatch(); st.Start(); }
            try
            {
                RunInCommand(cmd =>
                {
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        action.Invoke(new DBUtilDataReader { RawReader = reader, db = this });
                    }
                    finally
                    {
                        if (!reader.IsClosed)
                        {
                            try { reader.Close(); }
                            catch (Exception ex) { logger.LogError(ex, $"释放 DbDataReader 时报错:{ex.Message}"); };
                        }
                    }
                }, sql, commandType, timeoutSecond, parameters);
            }
            catch (Exception e)
            {
                if (Setting.HasMonitor) ex = e;
                throw;
            }
            finally
            {
                if (Setting.HasMonitor)
                {
                    if (st.IsRunning) st.Stop();
                    Setting.MonitorAction?.Invoke(new MonitorArgument
                    {
                        Sql = sql,
                        Exception = ex,
                        TimeSpan = TimeSpan.FromMilliseconds(st.ElapsedMilliseconds)
                    }).Wait();
                }
            }
        }
        #endregion

        #region SelectDataReader Func
        public virtual T SelectDataReader<T>(Func<DBUtilDataReader, T> func, string sql)
            => SelectDataReader(func, sql, null, null, []);

        public virtual T SelectDataReader<T>(Func<DBUtilDataReader, T> func, string sql, params DbParameter[] parameters)
            => SelectDataReader(func, sql, null, null, parameters);

        public virtual T SelectDataReader<T>(Func<DBUtilDataReader, T> func, string sql, IDictionary<string, object> parameters)
            => SelectDataReader(func, sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual T SelectDataReader<T>(Func<DBUtilDataReader, T> func, string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return (T)SelectDataReader(typeof(T), reader => func(reader), sql, commandType, timeoutSecond, parameters);
        }

        public virtual object SelectDataReader(Type type, Func<DBUtilDataReader, object> func, string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            if (func == null) throw new Exception("执行逻辑不能为空!");
            Stopwatch st = null;
            Exception ex = null;
            if (Setting.HasMonitor) { st = new Stopwatch(); st.Start(); }
            try
            {
                return RunInCommand(cmd =>
                {
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        object result = type.GetDefault();
                        if (func != null) result = func(new DBUtilDataReader { RawReader = reader, db = this });
                        return result;
                    }
                    finally
                    {
                        if (!reader.IsClosed)
                        {
                            try { reader.Close(); }
                            catch (Exception ex) { logger.LogError(ex, $"释放 DbDataReader 时报错:{ex.Message}"); };
                        }
                    }
                }, sql, commandType, timeoutSecond, parameters);
            }
            catch (Exception e)
            {
                if (Setting.HasMonitor) ex = e;
                throw;
            }
            finally
            {
                if (Setting.HasMonitor)
                {
                    if (st.IsRunning) st.Stop();
                    Setting.MonitorAction?.Invoke(new MonitorArgument
                    {
                        Sql = sql,
                        Exception = ex,
                        TimeSpan = TimeSpan.FromMilliseconds(st.ElapsedMilliseconds)
                    }).Wait();
                }
            }
        }
        #endregion

        #endregion

        #region SelectModel & SelectModelList

        #region SelectModel
        public virtual T SelectModel<T>(string sql)
            => SelectModel<T>(sql, null, null, []);

        public virtual T SelectModel<T>(string sql, IDictionary<string, object> parameters)
            => SelectModel<T>(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual T SelectModel<T>(string sql, params DbParameter[] parameters)
            => SelectModel<T>(sql, null, null, parameters);

        public virtual T SelectModel<T>(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return SelectDataReader<T>(reader => reader.ReadOne<T>(), sql, commandType, timeoutSecond, parameters);
        }
        public virtual object SelectModel(Type type, string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return SelectDataReader(type, (DBUtilDataReader reader) => reader.ReadOne(type), sql, commandType, timeoutSecond, parameters);
        }
        #endregion

        #region SelectModelList
        public virtual List<T> SelectModelList<T>(string sql)
            => SelectModelList<T>(sql, null, null, []);

        public virtual List<T> SelectModelList<T>(string sql, params DbParameter[] parameters)
           => SelectModelList<T>(sql, null, null, parameters);

        public virtual List<T> SelectModelList<T>(string sql, IDictionary<string, object> parameters)
          => SelectModelList<T>(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual List<T> SelectModelList<T>(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return SelectDataReader(reader => reader.ReadList<T>(), sql, commandType, timeoutSecond, parameters);
        }
        #endregion

        #endregion

        #region SelectDictionary & SelectDictionaryList

        #region SelectDictionary
        public virtual Dictionary<string, object> SelectDictionary(string sql)
            => SelectDictionary(sql, null, null, []);

        public virtual Dictionary<string, object> SelectDictionary(string sql, params DbParameter[] parameters)
            => SelectDictionary(sql, null, null, parameters);

        public virtual Dictionary<string, object> SelectDictionary(string sql, IDictionary<string, object> parameters)
            => SelectDictionary(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual Dictionary<string, object> SelectDictionary(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return SelectDataReader(reader =>
            {
                Dictionary<string, object> dic = null;
                var count = reader.RawReader.FieldCount;
                if (reader.RawReader.Read())
                {
                    dic = [];
                    for (int i = 0; i < count; i++)
                    {
                        dic[reader.RawReader.GetName(i)] = reader.RawReader.GetValue(i);
                    }
                }
                return dic;
            }, sql, commandType, timeoutSecond, parameters);
        }
        #endregion

        #region SelectDictionaryList
        public virtual List<Dictionary<string, object>> SelectDictionaryList(string sql)
            => SelectDictionaryList(sql, null, null, []);

        public virtual List<Dictionary<string, object>> SelectDictionaryList(string sql, params DbParameter[] parameters)
            => SelectDictionaryList(sql, null, null, parameters);

        public virtual List<Dictionary<string, object>> SelectDictionaryList(string sql, IDictionary<string, object> parameters)
            => SelectDictionaryList(sql, null, null, parameters?.Select(x => CreatePara(x.Key, x.Value)).ToArray());

        public virtual List<Dictionary<string, object>> SelectDictionaryList(string sql, CommandType? commandType, int? timeoutSecond, params DbParameter[] parameters)
        {
            return SelectDataReader(reader =>
            {
                var count = reader.RawReader.FieldCount;
                var names = new string[count];
                for (int i = 0; i < count; i++)
                {
                    names[i] = reader.RawReader.GetName(i);
                }
                var list = new List<Dictionary<string, object>>();
                while (reader.RawReader.Read())
                {
                    var dic = new Dictionary<string, object>();
                    list.Add(dic);
                    for (int i = 0; i < count; i++)
                    {
                        dic[names[i]] = reader.RawReader.GetValue(i);
                    }
                }
                return list;
            }, sql, commandType, timeoutSecond, parameters);
        }
        #endregion

        #endregion

        #region SelectMultiple
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2) SelectMultiple<T, T2>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
             {
                 var t1 = func(reader);
                 reader.RawReader.NextResult();
                 var t2 = func2(reader);
                 return (t1, t2);
             }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3) SelectMultiple<T, T2, T3>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                return (t1, t2, t3);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4) SelectMultiple<T, T2, T3, T4>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                return (t1, t2, t3, t4);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5) SelectMultiple<T, T2, T3, T4, T5>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                return (t1, t2, t3, t4, t5);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5, T6) SelectMultiple<T, T2, T3, T4, T5, T6>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , Func<DBUtilDataReader, T6> func6
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                reader.RawReader.NextResult();
                var t6 = func6(reader);
                return (t1, t2, t3, t4, t5, t6);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5, T6, T7) SelectMultiple<T, T2, T3, T4, T5, T6, T7>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , Func<DBUtilDataReader, T6> func6
            , Func<DBUtilDataReader, T7> func7
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                reader.RawReader.NextResult();
                var t6 = func6(reader);
                reader.RawReader.NextResult();
                var t7 = func7(reader);
                return (t1, t2, t3, t4, t5, t6, t7);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5, T6, T7, T8) SelectMultiple<T, T2, T3, T4, T5, T6, T7, T8>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , Func<DBUtilDataReader, T6> func6
            , Func<DBUtilDataReader, T7> func7
            , Func<DBUtilDataReader, T8> func8
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                reader.RawReader.NextResult();
                var t6 = func6(reader);
                reader.RawReader.NextResult();
                var t7 = func7(reader);
                reader.RawReader.NextResult();
                var t8 = func8(reader);
                return (t1, t2, t3, t4, t5, t6, t7, t8);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5, T6, T7, T8, T9) SelectMultiple<T, T2, T3, T4, T5, T6, T7, T8, T9>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , Func<DBUtilDataReader, T6> func6
            , Func<DBUtilDataReader, T7> func7
            , Func<DBUtilDataReader, T8> func8
            , Func<DBUtilDataReader, T9> func9
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                reader.RawReader.NextResult();
                var t6 = func6(reader);
                reader.RawReader.NextResult();
                var t7 = func7(reader);
                reader.RawReader.NextResult();
                var t8 = func8(reader);
                reader.RawReader.NextResult();
                var t9 = func9(reader);
                return (t1, t2, t3, t4, t5, t6, t7, t8, t9);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        /// <summary>
        /// 批量查询(返回多个结果集)
        /// </summary>
        public virtual (T, T2, T3, T4, T5, T6, T7, T8, T9, T10) SelectMultiple<T, T2, T3, T4, T5, T6, T7, T8, T9, T10>(string sql
            , Func<DBUtilDataReader, T> func
            , Func<DBUtilDataReader, T2> func2
            , Func<DBUtilDataReader, T3> func3
            , Func<DBUtilDataReader, T4> func4
            , Func<DBUtilDataReader, T5> func5
            , Func<DBUtilDataReader, T6> func6
            , Func<DBUtilDataReader, T7> func7
            , Func<DBUtilDataReader, T8> func8
            , Func<DBUtilDataReader, T9> func9
            , Func<DBUtilDataReader, T10> func10
            , int? timeoutSecond = null)
        {
            return SelectDataReader((reader) =>
            {
                var t1 = func(reader);
                reader.RawReader.NextResult();
                var t2 = func2(reader);
                reader.RawReader.NextResult();
                var t3 = func3(reader);
                reader.RawReader.NextResult();
                var t4 = func4(reader);
                reader.RawReader.NextResult();
                var t5 = func5(reader);
                reader.RawReader.NextResult();
                var t6 = func6(reader);
                reader.RawReader.NextResult();
                var t7 = func7(reader);
                reader.RawReader.NextResult();
                var t8 = func8(reader);
                reader.RawReader.NextResult();
                var t9 = func9(reader);
                reader.RawReader.NextResult();
                var t10 = func10(reader);
                return (t1, t2, t3, t4, t5, t6, t7, t8, t9, t10);
            }, sql, commandType: null, timeoutSecond: timeoutSecond);
        }
        #endregion

        #endregion
    }
}
